﻿ IF EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND name = 'P_GetUserApplicationList')
	BEGIN
		DROP  Procedure  [esf_sso].P_GetUserApplicationList
	END

GO

-- EXEC [esf_sso].P_GetUserApplicationList 892

CREATE Procedure [esf_sso].P_GetUserApplicationList
	@pPersonID			BIGINT = NULL
AS

	-- Usuario Publico
	IF @pPersonID IS NULL
	BEGIN
	
		SELECT DISTINCT
			VUM.Application_ID			ID
			,VUM.Application_Code		Code
			,VUM.Application_Name		Name
		FROM
			[esf_sso].VUserMenus VUM
		-- UserMenu , UserGroup (parent)
		INNER JOIN 
			[esf_sso].TreeBasicReference UG_UM ON
				UG_UM.BasicReferenceID = VUM.ChiBR_ID
		WHERE 
			UG_UM.ParentBasicReferenceID = (SELECT ID FROM [esf_sso].VUserGroup WHERE Code = 'Public')
			AND VUM.Application_IsEnabled = 1
			AND VUM.ChiBR_IsEnabled = 1
	
	END
	ELSE
	BEGIN
	
		SELECT DISTINCT
			VUM.Application_ID			ID
			,VUM.Application_Code		Code
			,VUM.Application_Name		Name
		FROM
			[esf_sso].VUserMenus VUM
		INNER JOIN 
			[esf_sso].TreeBasicReference UG_UM ON
				UG_UM.BasicReferenceID = VUM.ChiBR_ID
		INNER JOIN
			esf_sso.VUserGroup VUG ON VUG.ID = UG_UM.ParentBasicReferenceID
		INNER JOIN 
			[esf_sso].[User_BasicReference] UG ON UG.BasicReferenceID = VUG.ID
		WHERE 
			UG.UserPersonID = @pPersonID
			AND VUG.IsEnabled = 1
			AND VUM.Application_IsEnabled = 1
			AND VUM.ChiBR_IsEnabled = 1

	END
	
GO